<?php

require_once 'db_connect.php';

require_once 'Slim/Slim.php';
/*
 * Creating new Slim application
 */
$app = new Slim();
$db = new DB_CONNECT();
/*
 * Get topplace (depending on whether ID was provided)
 */

$app->get('/topplace(/:id)', function ($id = null) use ($app) {
    $data = array();
    if ($id === null) { 
		$query = 'SELECT COUNT(comment_id) AS total_comment, place.place_id, place.place_name, place.url_avatar, place.name_avatar, place.description
							FROM comment, place WHERE comment.place_id=place.place_id AND year(comment.time_comment) = year(NOW()) 
							AND month(comment.time_comment) = month(NOW()) AND place.delete <> 1 AND comment.delete <> 1 GROUP BY place_id ORDER BY total_comment DESC LIMIT 10';
		$result = mysql_query($query) or die(mysql_error());
        $data = array();
        if (mysql_num_rows($result) > 0) {
            $stt = 1;		
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'stt'			=>		$stt,
					'place_id' =>			$row['place_id'],
					'total_comment' =>     	$row['total_comment'],
					'place_name' =>       	$row['place_name'],
					'url_avatar' =>			$row['url_avatar'],
					'name_avatar' =>			$row['name_avatar'],
					'description' =>		$row['description']
					);
					$stt++;
                }
        }
    } else {
        $data = array();
		$query = 'SELECT COUNT(comment_id) AS total_comment, place.place_id, place.place_name, place.url_avatar, place.name_avatar, place.description
					FROM comment, place WHERE comment.place_id='.$id.' AND comment.place_id=place.place_id AND year(comment.time_comment) = year(NOW()) 
					AND month(comment.time_comment) = month(NOW()) AND place.delete <> 1 AND comment.delete <> 1 GROUP BY place_id ORDER BY total_comment DESC LIMIT 10';
		$result = mysql_query($query) or die(mysql_error());
		if (mysql_num_rows($result) > 0) {			
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'place_id' =>			$row['place_id'],
					'total_comment' =>     	$row['total_comment'],
					'place_name' =>       	$row['place_name'],
					'url_avatar' =>			$row['url_avatar'],
					'name_avatar' =>			$row['name_avatar'],
					'description' =>		$row['description']
				);
                }
        } 
    }
    $app->response()->header('Content-Type', 'application/json');
    echo json_encode($data);
});

//Get NewFeed
$app->get('/newfeed(/:id)', function ($id = null) use ($app) {
	$data = array();
	if($id === null){		
		$query = 'SELECT user.user_avatar_zing, place.place_name, place.place_id, user.user_id_zing, user.user_name_zing, user.user_display_name, comment.detail_comment, comment.time_comment '
			.'FROM comment, place, user WHERE comment.user_id_zing = user.user_id_zing '
            .'and comment.place_id = place.place_id AND place.delete <> 1 AND comment.delete <> 1 AND user.delete <> 1 AND comment.type = 1 ' 
			.'ORDER BY comment.time_comment DESC LIMIT 10';
		$result = mysql_query($query) or die(mysql_error());
        $data = array();
        if (mysql_num_rows($result) > 0) {
            $stt = 1;		
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'stt'				=>		$stt,
					'user_id_zing' 			=>		$row['user_id_zing'],
					'user_avatar_zing' 	=>     	$row['user_avatar_zing'],
					'user_name_zing'	 =>     $row['user_name_zing'],
					'user_display_name' =>		$row['user_display_name'],
					'place_name' 		=>		$row['place_name'],
					'place_id' 			=>		$row['place_id'],
					'detail_comment' 	=>		$row['detail_comment'],
					'time_comment'		=>		$row['time_comment']
					);
					$stt++;
                }
        }
	}else {
        $data = array();
		$query = 'SELECT user.user_avatar_zing, place.place_name, place.place_id, user.user_id_zing, user.user_name_zing, user.user_display_name, comment.detail_comment, comment.time_comment '
			.'FROM comment, place, user WHERE comment.user_id_zing = user.user_id_zing and comment.comment_id='.$id.' and comment.place_id = place.place_id AND place.delete <> 1 AND comment.delete <> 1 AND user.delete <> 1 AND comment.type = 1'
			.' ORDER BY comment.time_comment DESC LIMIT 10';
		$result = mysql_query($query) or die(mysql_error());
		if (mysql_num_rows($result) > 0) {			
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'user_id_zing' 			=>		$row['user_id_zing'],
					'user_avatar_zing' 	=>     	$row['user_avatar_zing'],
					'user_name_zing'	 =>     $row['user_name_zing'],
					'user_display_name' =>		$row['user_display_name'],
					'place_name' 		=>		$row['place_name'],
					'place_id' 			=>		$row['place_id'],
					'detail_comment' 	=>		$row['detail_comment'],
					'time_comment'		=> 		$row['time_comment']
					);
            }
        } 
    }
    $app->response()->header('Content-Type', 'application/json');
    echo json_encode($data);
});

$app->get('/foodcategories(/:id)', function ($id = null) use ($app) {
	$data = array();
	if($id === null){		
		$query = 'SELECT category_place_id, category_place_name FROM category_place';
		$result = mysql_query($query) or die(mysql_error());
        $data = array();
        if (mysql_num_rows($result) > 0) {		
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'category_place_id' =>		$row['category_place_id'],
					'category_place_name' 	=>     	$row['category_place_name']
					);
                }
        }
	}else {
        $data = array();
		$query = 'SELECT category_place_id, category_place_name FROM category_place WHERE category_place_id='.$id;
		$result = mysql_query($query) or die(mysql_error());
		if (mysql_num_rows($result) > 0) {			
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'category_place_id' =>		$row['category_place_id'],
					'category_place_name' 	=>     	$row['category_place_name']
					);
            }
        } 
    }
    $app->response()->header('Content-Type', 'application/json');
    echo json_encode($data);
});

//get newplace
$app->get('/newplace(/:id)', function ($id = null) use ($app) {
	$data = array();
	if($id === null){		
		$query = 'SELECT place.place_id, place.place_name, place.url_avatar, place.name_avatar, place.date_create FROM place '
		.'WHERE place.delete <> 1 AND place.place_id NOT IN (SELECT report.place_id FROM report)'
		.'ORDER BY place.date_create DESC LIMIT 3';
		$result = mysql_query($query) or die(mysql_error());
        $data = array();
        if (mysql_num_rows($result) > 0) {		
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'place_id' =>		$row['place_id'],
					'place_name' 	=>     	$row['place_name'],
					'url_avatar'	 =>		$row['url_avatar'],
					'name_avatar'	=>		$row['name_avatar'],
					'date_create'	=>		$row['date_create']
					);
                }
        }
	}else {
        $data = array();
		$query = 'SELECT place.place_id, place.place_name, place.url_avatar, place.name_avatar, place.date_create FROM place '
		.'WHERE place.delete <> 1 AND place.place_id ='.$id.' and place.place_id NOT IN (SELECT report.place_id FROM report)'
		.'ORDER BY place.date_create DESC LIMIT 3';
		$result = mysql_query($query) or die(mysql_error());
		if (mysql_num_rows($result) > 0) {			
                while ($row = mysql_fetch_array($result)) {
					$data[] = array(
					'place_id' =>		$row['place_id'],
					'place_name' 	=>     	$row['place_name'],
					'url_avatar'	 =>		$row['url_avatar'],
					'name_avatar'	=>		$row['name_avatar'],
					'date_create'	=>		$row['date_create']
					);
            }
        } 
    }
    $app->response()->header('Content-Type', 'application/json');
    echo json_encode($data);
});

/*
 * Runing the Slim app
 */
$app->run();